package cn.com.jsepc.rep.report;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.demension.CheckLeaf;
import cn.com.jsepc.rep.vo.FactTableVO;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;

import com.jarp.db.DAO;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class CreateXLS {
    public static String DemIsNotSelect="99999";
    public String create(OutputStream out, String repid, String corpid, String demJsonArr) throws IOException {
        String repname = "";
        String rep_danwei = "select t.DATAUNIT from tb_report t where t.REPID=" + repid;
        String res = "";
        DAO dao = new DAO();
        String getrepname = "select repname from tb_report where repid=" + repid;

        try {
            repname = dao.getDataValue(ConstUtil.DBNAME, getrepname);
            rep_danwei = "(" + dao.getDataValue(ConstUtil.DBNAME, rep_danwei) + ")";
        } catch (ServletException e1) {
            e1.printStackTrace();
        }
        String sql = "";
        StringBuffer demwhereBuffer = new StringBuffer();
        if (demJsonArr != null && !demJsonArr.equals("")) {
            JSONObject qryjson = JSONObject.fromString(demJsonArr);
            JSONArray demArr = qryjson.getJSONArray("demlist");
            for (int i = 0; i < demArr.length(); i++) {
                JSONObject jsonDem = demArr.getJSONObject(i);
                String facdimtable = jsonDem.getString("facdimtable");
                String facdimcodecol = jsonDem.getString("facdimcodecol");
                String facdimvalue = jsonDem.getString("facdimvalue");
                if (facdimvalue != null && !facdimvalue.equals("")) {
                    demwhereBuffer.append("\n and " + facdimtable + "." + facdimcodecol + " in (" + facdimvalue + ")");
                }
            }
        } else {
            System.out.println("demJsonArr is null");
        }
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
//	            String issum = repVo.getIssum();//1聚合0不聚合
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;
//	            boolean bIsSum = false;//是否聚合
//	            if (issum!=null && issum.equals("1")){
//	            	bIsSum = true;
//	            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename != null && faccol != null && repDemLst != null && repDemLst.size() > 0) {
                StringBuffer ssql = new StringBuffer();
                StringBuffer ssqlTot = new StringBuffer();
                StringBuffer sdemcol = new StringBuffer();
                StringBuffer sdemtab = new StringBuffer();
                StringBuffer sdemon = new StringBuffer();
                StringBuffer sorder = new StringBuffer();

                ssql.append("select * from                ");
                ssql.append("(select a." + facsumcol +",a.YOYRATION,a.MONRATION,a.TYOYRATION,a.TMONRATION,a.PREVAL,a.TPREVAL,a.TSUMVAL,TREPVAL ");
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimtable = repDemVO.getDimtable(); //td_org
                    String dimidcol = repDemVO.getIdcol();
                    String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                    String dimcodecol = repDemVO.getCodecol();
                    String dimnamecol = repDemVO.getNamecol();
                    ssql.append("\n ,z." + facdimcol);
                    ssql.append("\n ,z." + facdimcol + dimnamecol);
                    if (j != 0) {
                        sdemtab.append(",");
                        sdemon.append(" and ");
                        sorder.append(",");
                        sdemcol.append("\n,");
                    }
                    sdemcol.append("" + dimtable + "." + dimidcol + " as " + facdimcol);
                    sdemcol.append("\n," + dimtable + "." + dimnamecol + " as " + facdimcol + dimnamecol);

                    sdemtab.append(dimtable);
                    sdemon.append(" z." + facdimcol + " = a." + facdimcol);
                    sorder.append(facdimcol);
                    String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
                    if (dimtype != null) {
                        if (dimtype.equals("1")) {
                            if (dimcodecol != null && !dimcodecol.equals("")) {
                                bDwDim = true;
                                dwDimCodeVal = getDimCodeByID(dimtable, dimidcol, dimcodecol, corpid);
                                dwDimCodeCol = dimtable + "." + dimcodecol;
                            }
                        }
                    }
                }
                ssql.append("\n,a." + faccol + " from (select ");
                ssql.append(sdemcol.toString());
                ssql.append("\n from " + sdemtab.toString() + " where 1=1 ");
                if (corpid != null && !corpid.equals("") && bDwDim) {
                    if (dwDimCodeVal != null && !dwDimCodeVal.equals("")) {
                        ssql.append(" and " + dwDimCodeCol + " like '" + dwDimCodeVal + "%'");
                    }
                }
                ssql.append(demwhereBuffer.toString());
                ssql.append("\n ) z ");
                ssql.append("\n left join " + tablename + " a on " + sdemon);
                ssql.append("\n order by " + sorder.toString() + ")t");
                System.out.println(sql.toString());
                sql = ssql.toString();	
            }
        }


        List<String> count_name = new ArrayList<String>();;    //用来记录具体的列名
        String colSql = "select factdimcol,availsname,d.NAMECOL "
                + "from tb_rep_demension t "
                + "LEFT JOIN tb_demension d on t.DIMID=d.DEMID "
                + "where repid=" + repid + " order by dimorder";
        Vector col_v;
        try {
            col_v = dao.getDataSet(ConstUtil.DBNAME, colSql);
            for (int i = 0; i < col_v.size(); i++) {
                Hashtable h = (Hashtable) col_v.get(i);
                String col_code = h.get("FACTDIMCOL").toString();
                String col_name = h.get("AVAILSNAME").toString();
                String name_col = h.get("NAMECOL").toString();
                //Name
                count_name.add(col_code + name_col + "," + col_name);
                //ID
                count_name.add(col_code + "," + col_name + "编号");

            }
            count_name.add("REPVAL,当期值");
            count_name.add("SUMVAL,累计值");
            count_name.add("YOYRATION,同比");
            count_name.add("MONRATION,环比");
            count_name.add("TYOYRATION,同比累计");
            count_name.add("TMONRATION,环比累计");
            count_name.add("PREVAL,上期值");
            count_name.add("TPREVAL,上期累计");
            count_name.add("TREPVAL,同期值");
            count_name.add("TSUMVAL,同期累计");
        } catch (ServletException ex) {
            Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
        }

        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(repname, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {
                sheet.addCell(new Label(i, 0, count_name.get(i).split(",")[1]));
            } catch (Exception ex) {
                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        int hang = 1;     //数据应该写在多少行
        try {
            int count = dao.getDataSetCount("Jarp", sql);
            int ipage = 0;
            //(count%1000>0):ipage=count/1000?ipage=count/1000+1;
            if (count % 1000 > 0) {
                ipage = count / 1000 + 1;
            } else {
                ipage = count / 1000;
            }
            int sheetcount = 1;
            for (int page = 1; page <= ipage; page++) {
                Vector v = dao.getDataSet("Jarp", sql, page, 1000);
                for (int i = 0; i < v.size(); i++) {
                    //判断是否要新建页
                    if (hang == 10001) {
                        hang = 1;
                        sheetcount++;
                        sheet = book.createSheet("sheet" + sheetcount, sheetcount);
                        for (int j = 0; j < count_name.size(); j++) {
                            try {
                                sheet.addCell(new Label(j, 0, count_name.get(j).split(",")[1]));
                            } catch (Exception ex) {
                                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
                            }
                        }
                    }
                    Hashtable hash = (Hashtable) v.get(i);
                    for (int i2 = 0; i2 < count_name.size(); i2++) {
                        //String thisvalue=(String)hash.get(row[i2]);
                        String key = count_name.get(i2).split(",")[0];
                        String thisvalue = (String) hash.get(key);
                        if (thisvalue==null || thisvalue.equals("")) {
                            thisvalue = "";
                        }
                        sheet.addCell(new Label(i2, hang, thisvalue));
                    }
                    hang++;
                }
            }
            //
            book.write();
            book.close();
            res = "ok";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public String create_2(OutputStream out, String repid, String corpid, String demJsonArr) throws IOException {
        String res = "";
        String sql="";
      String factsql=this.getDataGrid(repid, corpid, demJsonArr);
        DAO dao = new DAO();
        JSONArray json = new JSONArray();
        String totalCount = null;
        List<String> doNotWrite=new ArrayList<String>();     //Excel里不需要写的列
        try {

            StringBuffer demwhereBuffer = new StringBuffer();
            if (demJsonArr!=null && !demJsonArr.equals("")){
            	JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtable");
	                String facdimcodecol = jsonDem.getString("facdimcodecol");
	                String facdimvalue = jsonDem.getString("facdimvalue");
                        String facdimfield=jsonDem.getString("facdimfield");
	                if (!facdimvalue.equals(this.DemIsNotSelect)){
	                	demwhereBuffer.append("\n and "+facdimtable+"."+facdimcodecol+" in ("+facdimvalue+")");
                                doNotWrite.add(facdimfield);
	                }
                        
	            }
            }else{
            	System.out.println("demJsonArr is null");
            }
            RepDataVO repVo = getRepDemension(repid);
            if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;

            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename!=null && faccol != null && repDemLst!=null && repDemLst.size()>0) {
            StringBuffer ssql = new StringBuffer();
            StringBuffer ssqlTot = new StringBuffer();
            StringBuffer sdemcol = new StringBuffer();
            StringBuffer sdemtab = new StringBuffer();
            StringBuffer sdemon = new StringBuffer();
            StringBuffer sorder = new StringBuffer();
            ssql.append("select a."+faccol+",a."+facsumcol+",a.YOYRATION,a.MONRATION,a.TYOYRATION,a.TMONRATION,a.PREVAL,a.TPREVAL,a.TSUMVAL,a.TREPVAL  ");
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtable = repDemVO.getDimtable(); //td_org
                String dimidcol = repDemVO.getIdcol();
                String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                String dimcodecol = repDemVO.getCodecol();
                String dimnamecol = repDemVO.getNamecol();
                boolean b=this.isSelect(facdimcol, demJsonArr);
                if(b==true){
                ssql.append("\n ,z."+facdimcol);
                ssql.append("\n ,z."+facdimcol+dimnamecol);
                
                
                if (j!=0){
                	sdemtab.append(",");
                	sdemon.append(" and ");
                	sorder.append(",");
                	sdemcol.append("\n,");
                }
                sdemcol.append(""+dimtable+"."+dimidcol +" as "+ facdimcol);
                sdemcol.append("\n,"+dimtable+"."+dimnamecol +" as "+ facdimcol+dimnamecol);
                
                sdemtab.append(dimtable);
                sdemon.append(" z."+facdimcol + " = a."+facdimcol);
                sorder.append(facdimcol);
	            String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
	            if (dimtype!=null){
	              	if (dimtype.equals("1")){	                	
		                	if (dimcodecol!=null && !dimcodecol.equals("")){
		                		bDwDim = true;
		                		dwDimCodeVal = getDimCodeByID(dimtable,dimidcol,dimcodecol,corpid);
		                		dwDimCodeCol = dimtable+"."+dimcodecol;
		                	}
	              	}
	              }}
                else{
                ssql.append("\n ,"+DemIsNotSelect+" as "+facdimcol);
                ssql.append("\n ,"+DemIsNotSelect+" as "+facdimcol+dimnamecol);
                   if (j!=0){
                	sdemon.append(" and ");
                }
                  sdemon.append( " a."+facdimcol+"="+this.DemIsNotSelect);    
                    
                }
            }            
            ssql.append("\n from (select ");    
            sdemcol=this.clear_char(sdemcol);
            sdemtab=this.clear_char(sdemtab);
            sorder=this.clear_char(sorder);
            ssql.append(sdemcol.toString()); 
            ssql.append("\n from "+sdemtab.toString() + " where 1=1 ");     
            if (corpid!=null && !corpid.equals("") && bDwDim){
            	if (dwDimCodeVal!=null && !dwDimCodeVal.equals("")){
            		ssql.append(" and "+dwDimCodeCol+" like '"+dwDimCodeVal+"%'");
            	}
              
            }
            ssql.append(demwhereBuffer.toString());
            ssql.append("\n ) z ");     
            ssql.append("\n left join "+tablename +" a on "+sdemon);
            ssql.append("\n order by "+sorder.toString());
           sql=ssql.toString();
            }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    

        List<String> count_name = new ArrayList<String>();;    //用来记录具体的列名
        String colSql = "select factdimcol,availsname,d.NAMECOL "
                + "from tb_rep_demension t "
                + "LEFT JOIN tb_demension d on t.DIMID=d.DEMID "
                + "where repid=" + repid + " order by dimorder";
        Vector col_v;
        try {
            col_v = dao.getDataSet(ConstUtil.DBNAME, colSql);
            for (int i = 0; i < col_v.size(); i++) {
                Hashtable h = (Hashtable) col_v.get(i);
                String col_code = h.get("FACTDIMCOL").toString();
                boolean b=this.doNotWrite(col_code, doNotWrite);
                if(b==true){
                    continue;
                }
                String col_name = h.get("AVAILSNAME").toString();
                String name_col = h.get("NAMECOL").toString();
                //Name
                count_name.add(col_code + name_col + "," + col_name);
                //ID
                count_name.add(col_code + "," + col_name + "编号");

            }
            count_name.add("REPVAL,当期值");
            count_name.add("SUMVAL,累计值");
            count_name.add("YOYRATION,同比");
            count_name.add("MONRATION,环比");
            count_name.add("TYOYRATION,累计同比");
            count_name.add("TMONRATION,累计环比");
            count_name.add("PREVAL,上期值");
            count_name.add("TPREVAL,上期累计");
            count_name.add("TREPVAL,同期值");
            count_name.add("TSUMVAL,同期累计");
        } catch (ServletException ex) {
            Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
        }

        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet("sheet1", 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {
                sheet.addCell(new Label(i, 0, count_name.get(i).split(",")[1]));
            } catch (Exception ex) {
                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        int hang = 1;     //数据应该写在多少行
        try {
            int count = dao.getDataSetCount("Jarp", sql);
            int ipage = 0;
            //(count%1000>0):ipage=count/1000?ipage=count/1000+1;
            if (count % 1000 > 0) {
                ipage = count / 1000 + 1;
            } else {
                ipage = count / 1000;
            }
            int sheetcount = 1;
            for (int page = 1; page <= ipage; page++) {
                Vector v = dao.getDataSet("Jarp", sql, page, 1000);
                for (int i = 0; i < v.size(); i++) {
                    //判断是否要新建页
                    if (hang == 10001) {
                        hang = 1;
                        sheetcount++;
                        sheet = book.createSheet("sheet" + sheetcount, sheetcount);
                        for (int j = 0; j < count_name.size(); j++) {
                            try {
                                sheet.addCell(new Label(j, 0, count_name.get(j).split(",")[1]));
                            } catch (Exception ex) {
                                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
                            }
                        }
                    }
                    Hashtable hash = (Hashtable) v.get(i);
                    for (int i2 = 0; i2 < count_name.size(); i2++) {
                        //String thisvalue=(String)hash.get(row[i2]);
                        String key = count_name.get(i2).split(",")[0];
                        String thisvalue = (String) hash.get(key);
                        if (thisvalue.equals(null) || thisvalue.equals("")||thisvalue.equals(this.DemIsNotSelect)) {
                            thisvalue = "";
                        }
                        sheet.addCell(new Label(i2, hang, thisvalue));
                    }
                    hang++;
                }
            }
            //
            book.write();
            book.close();
            res = "ok";
        } catch (Exception e) {
            e.printStackTrace();
        }
       
      return res;   
    }

    /**
     * 导出数据采集模板，该方法是普通筛选维度和筛选完成值的结合
     * @param out
     * @param repid
     * @param corpid
     * @param demJsonArr
     * @return
     * @throws IOException 
     */
    public String create3(OutputStream out, String repid, String corpid, String demJsonArr) throws IOException{
    	String repname = "";
        String rep_danwei = "select t.DATAUNIT from tb_report t where t.REPID=" + repid;
        String res = "";
        DAO dao = new DAO();
        String getrepname = "select repname from tb_report where repid=" + repid;

        try {
            repname = dao.getDataValue(ConstUtil.DBNAME, getrepname);
            rep_danwei = "(" + dao.getDataValue(ConstUtil.DBNAME, rep_danwei) + ")";
        } catch (ServletException e1) {
            e1.printStackTrace();
        }
        String sql = ""; //普通的筛选维度
        String sql_wan="";
        StringBuffer demwhereBuffer = new StringBuffer();
        if (demJsonArr != null && !demJsonArr.equals("")) {
            JSONObject qryjson = JSONObject.fromString(demJsonArr);
            JSONArray demArr = qryjson.getJSONArray("demlist");
            for (int i = 0; i < demArr.length(); i++) {
                JSONObject jsonDem = demArr.getJSONObject(i);
                String facdimtable = jsonDem.getString("facdimtable_weidu");
                String facdimcodecol = jsonDem.getString("facdimcodecol_weidu");
                String facdimvalue = jsonDem.getString("facdimvalue_weidu");
                if (facdimvalue != null && !facdimvalue.equals("")) {
                    demwhereBuffer.append("\n and " + facdimtable + "." + facdimcodecol + " in (" + facdimvalue + ")");
                }
            }
        } else {
            System.out.println("demJsonArr is null");
        }
        RepDataVO repVo = getRepDemension(repid);
        if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
//	            String issum = repVo.getIssum();//1聚合0不聚合
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;
//	            boolean bIsSum = false;//是否聚合
//	            if (issum!=null && issum.equals("1")){
//	            	bIsSum = true;
//	            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename != null && faccol != null && repDemLst != null && repDemLst.size() > 0) {
                StringBuffer ssql = new StringBuffer();
                StringBuffer ssqlTot = new StringBuffer();
                StringBuffer sdemcol = new StringBuffer();
                StringBuffer sdemtab = new StringBuffer();
                StringBuffer sdemon = new StringBuffer();
                StringBuffer sorder = new StringBuffer();

                ssql.append("select * from                ");
                ssql.append("(select a." + facsumcol +",a.YOYRATION,a.MONRATION,a.TYOYRATION,a.TMONRATION,a.PREVAL,a.TPREVAL,a.TSUMVAL,TREPVAL ");
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimtable = repDemVO.getDimtable(); //td_org
                    String dimidcol = repDemVO.getIdcol();
                    String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                    String dimcodecol = repDemVO.getCodecol();
                    String dimnamecol = repDemVO.getNamecol();
                    ssql.append("\n ,z." + facdimcol);
                    ssql.append("\n ,z." + facdimcol + dimnamecol);
                    if (j != 0) {
                        sdemtab.append(",");
                        sdemon.append(" and ");
                        sorder.append(",");
                        sdemcol.append("\n,");
                    }
                    sdemcol.append("" + dimtable + "." + dimidcol + " as " + facdimcol);
                    sdemcol.append("\n," + dimtable + "." + dimnamecol + " as " + facdimcol + dimnamecol);

                    sdemtab.append(dimtable);
                    sdemon.append(" z." + facdimcol + " = a." + facdimcol);
                    sorder.append(facdimcol);
                    String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
                    if (dimtype != null) {
                        if (dimtype.equals("1")) {
                            if (dimcodecol != null && !dimcodecol.equals("")) {
                                bDwDim = true;
                                dwDimCodeVal = getDimCodeByID(dimtable, dimidcol, dimcodecol, corpid);
                                dwDimCodeCol = dimtable + "." + dimcodecol;
                            }
                        }
                    }
                }
                ssql.append("\n,a." + faccol + " from (select ");
                ssql.append(sdemcol.toString());
                ssql.append("\n from " + sdemtab.toString() + " where 1=1 ");
                if (corpid != null && !corpid.equals("") && bDwDim) {
                    if (dwDimCodeVal != null && !dwDimCodeVal.equals("")) {
                        ssql.append(" and " + dwDimCodeCol + " like '" + dwDimCodeVal + "%'");
                    }
                }
                ssql.append(demwhereBuffer.toString());
                ssql.append("\n ) z ");
                ssql.append("\n left join " + tablename + " a on " + sdemon);
                ssql.append("\n order by " + sorder.toString() + ")t");
                sql = ssql.toString();
                System.out.println(sql);
            }
        }

        List<String> doNotWrite=new ArrayList<String>();     //Excel里不需要写的列
        try {
            StringBuffer demwhereBuffer_wan = new StringBuffer();
            if (demJsonArr!=null && !demJsonArr.equals("")){
            	JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtablewan");
	                String facdimcodecol = jsonDem.getString("facdimcodecolwan");
	                String facdimvalue = jsonDem.getString("facdimvaluewan");
                        String facdimfield=jsonDem.getString("facdimfieldwan");
	                if (!facdimvalue.equals("")){
	                	demwhereBuffer_wan.append("\n and "+facdimtable+"."+facdimcodecol+" in ("+facdimvalue+")");
                                doNotWrite.add(facdimfield);
	                }
	            }
            }else{
            	System.out.println("demJsonArr is null");
            }
            if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;

            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename!=null && faccol != null && repDemLst!=null && repDemLst.size()>0) {
            StringBuffer ssql_wan = new StringBuffer();
            StringBuffer ssqlTot = new StringBuffer();
            StringBuffer sdemcol = new StringBuffer();
            StringBuffer sdemtab = new StringBuffer();
            StringBuffer sdemon = new StringBuffer();
            StringBuffer sorder = new StringBuffer();
            ssql_wan.append("select a."+faccol+",a."+facsumcol+",a.YOYRATION,a.MONRATION,a.TYOYRATION,a.TMONRATION,a.PREVAL,a.TPREVAL,a.TSUMVAL,a.TREPVAL  ");
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtable = repDemVO.getDimtable(); //td_org
                String dimidcol = repDemVO.getIdcol();
                String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                String dimcodecol = repDemVO.getCodecol();
                String dimnamecol = repDemVO.getNamecol();
                boolean b=this.isSelect2(facdimcol, demJsonArr);
                if(b==true){
                ssql_wan.append("\n ,z."+facdimcol);
                ssql_wan.append("\n ,z."+facdimcol+dimnamecol);
                
                
                if (j!=0){
                	sdemtab.append(",");
                	sdemon.append(" and ");
                	sorder.append(",");
                	sdemcol.append("\n,");
                }
                sdemcol.append(""+dimtable+"."+dimidcol +" as "+ facdimcol);
                sdemcol.append("\n,"+dimtable+"."+dimnamecol +" as "+ facdimcol+dimnamecol);
                
                sdemtab.append(dimtable);
                sdemon.append(" z."+facdimcol + " = a."+facdimcol);
                sorder.append(facdimcol);
	            String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
	            if (dimtype!=null){
	              	if (dimtype.equals("1")){	                	
		                	if (dimcodecol!=null && !dimcodecol.equals("")){
		                		bDwDim = true;
		                		dwDimCodeVal = getDimCodeByID(dimtable,dimidcol,dimcodecol,corpid);
		                		dwDimCodeCol = dimtable+"."+dimcodecol;
		                	}
	              	}
	              }}
                else{
                ssql_wan.append("\n ,'' as "+facdimcol);
                ssql_wan.append("\n ,'' as "+facdimcol+dimnamecol);
                   if (j!=0){
                	sdemon.append(" and ");
                }
                  sdemon.append( " a."+facdimcol+"="+this.DemIsNotSelect);    
                    
                }
            }            
            ssql_wan.append("\n from (select ");    
            sdemcol=this.clear_char(sdemcol);
            sdemtab=this.clear_char(sdemtab);
            sorder=this.clear_char(sorder);
            ssql_wan.append(sdemcol.toString()); 
            ssql_wan.append("\n from "+sdemtab.toString() + " where 1=1 ");     
            if (corpid!=null && !corpid.equals("") && bDwDim){
            	if (dwDimCodeVal!=null && !dwDimCodeVal.equals("")){
            		ssql_wan.append(" and "+dwDimCodeCol+" like '"+dwDimCodeVal+"%'");
            	}
              
            }
            ssql_wan.append(demwhereBuffer_wan.toString());
            ssql_wan.append("\n ) z ");     
            ssql_wan.append("\n left join "+tablename +" a on "+sdemon);
            ssql_wan.append("\n order by "+sorder.toString());
           sql_wan=ssql_wan.toString();
           System.out.println(sql_wan);
            }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        List<String> count_name = new ArrayList<String>();;    //用来记录具体的列名
        String colSql = "select factdimcol,availsname,d.NAMECOL "
                + "from tb_rep_demension t "
                + "LEFT JOIN tb_demension d on t.DIMID=d.DEMID "
                + "where repid=" + repid + " order by dimorder";
        Vector col_v;
        try {
            col_v = dao.getDataSet(ConstUtil.DBNAME, colSql);
            for (int i = 0; i < col_v.size(); i++) {
                Hashtable h = (Hashtable) col_v.get(i);
                String col_code = h.get("FACTDIMCOL").toString();
                String col_name = h.get("AVAILSNAME").toString();
                String name_col = h.get("NAMECOL").toString();
                //Name
                count_name.add(col_code + name_col + "," + col_name);
                //ID
                count_name.add(col_code + "," + col_name + "编号");

            }
            count_name.add("REPVAL,当期值");
            count_name.add("SUMVAL,累计值");
            count_name.add("PREVAL,上期值");
            count_name.add("TPREVAL,上期累计");
            count_name.add("TREPVAL,同期值");
            count_name.add("TSUMVAL,同期累计");
        } catch (ServletException ex) {
            Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
        }

        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(repname, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {
                sheet.addCell(new Label(i, 0, count_name.get(i).split(",")[1]));
            } catch (Exception ex) {
                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        int hang = 1;     //数据应该写在多少行
        try {
        	int count1=dao.getDataSetCount(ConstUtil.DBNAME, sql);
        	int count2=dao.getDataSetCount(ConstUtil.DBNAME, sql_wan);
            int count = count1+count2;
            int ipage = 0;
            //(count%1000>0):ipage=count/1000?ipage=count/1000+1;
            if (count % 1000 > 0) {
                ipage = count / 1000 + 1;
            } else {
                ipage = count / 1000;
            }
            int page1=count1%1000==0?count1/1000:count1/1000+1;
            int page2=count2%1000==0?count2/1000:count2/1000+1;
            int sheetcount = 1;
            for (int page = 1; page <= page1; page++) {
                Vector v = dao.getDataSet("Jarp", sql, page, 1000);
                for (int i = 0; i < v.size(); i++) {
                    //判断是否要新建页
                    if (hang == 10001) {
                        hang = 1;
                        sheetcount++;
                        sheet = book.createSheet("sheet" + sheetcount, sheetcount);
                        for (int j = 0; j < count_name.size(); j++) {
                            try {
                                sheet.addCell(new Label(j, 0, count_name.get(j).split(",")[1]));
                            } catch (Exception ex) {
                                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
                            }
                        }
                    }
                    Hashtable hash = (Hashtable) v.get(i);
                    for (int i2 = 0; i2 < count_name.size(); i2++) {
                        //String thisvalue=(String)hash.get(row[i2]);
                        String key = count_name.get(i2).split(",")[0];
                        String thisvalue = (String) hash.get(key);
                        if (thisvalue==null || thisvalue.equals("")) {
                            thisvalue = "";
                        }
                        sheet.addCell(new Label(i2, hang, thisvalue));
                    }
                    hang++;
                }
            }
            
            for (int page = 1; page <= page2; page++) {
                Vector v = dao.getDataSet("Jarp", sql_wan, page, 1000);
                if(v!=null){
                for (int i = 0; i < v.size(); i++) {
                    //判断是否要新建页
                    if (hang == 10001) {
                        hang = 1;
                        sheetcount++;
                        sheet = book.createSheet("sheet" + sheetcount, sheetcount);
                        for (int j = 0; j < count_name.size(); j++) {
                            try {
                                sheet.addCell(new Label(j, 0, count_name.get(j).split(",")[1]));
                            } catch (Exception ex) {
                                Logger.getLogger(CreateXLS.class.getName()).log(Level.SEVERE, null, ex);
                            }
                        }
                    }
                    Hashtable hash = (Hashtable) v.get(i);
                    for (int i2 = 0; i2 < count_name.size(); i2++) {
                        //String thisvalue=(String)hash.get(row[i2]);
                        String key = count_name.get(i2).split(",")[0];
                        String thisvalue = (String) hash.get(key);
                        if (thisvalue==null || thisvalue.equals("")) {
                            thisvalue = "";
                        }
                        sheet.addCell(new Label(i2, hang, thisvalue));
                    }
                    hang++;
                }
            }
            }
            //
            book.write();
            book.close();
            res = "ok";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }
    public RepDataVO getRepDemension(String repid) {
        RepDataVO repVO = new RepDataVO();
        DAO dao = new DAO();
        try {
            String sql = "select * from ( select * from tb_report where repid=" + repid + " and ISVALID=1 ) a"
                    + " left join tb_rep_demension b on b.repid=a.repid"
                    + " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
//	            System.out.println("sql:" + sql);
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    RepDemensionVO repDemVO = new RepDemensionVO();
                    repDemVO.setId((String) hashRows.get("ID"));
                    repDemVO.setRepid((String) hashRows.get("REPID"));
                    repDemVO.setDimid((String) hashRows.get("DIMID"));
                    repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    repDemVO.setDemname((String) hashRows.get("DEMNAME"));
                    repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL")).toUpperCase());
                    repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
                    repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
                    repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
                    repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    repDemVO.setIdcol(((String) hashRows.get("IDCOL")).toUpperCase());
                    repDemVO.setNamecol(((String) hashRows.get("NAMECOL")).toUpperCase());
                    repDemVO.setParentcol(((String) hashRows.get("PARENTCOL")).toUpperCase());
                    repDemVO.setCodecol(((String) hashRows.get("CODECOL")).toUpperCase());
                    repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
                    repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
                    repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
                    repDemLst.add(repDemVO);
                    if (i == v.size() - 1) {
                        repVO.setRepid((String) hashRows.get("REPID"));
                        repVO.setRepcode((String) hashRows.get("REPCODE"));
                        repVO.setRepname((String) hashRows.get("REPNAME"));
                        repVO.setCataid((String) hashRows.get("CATAID"));
                        repVO.setRepdesc((String) hashRows.get("REPDESC"));
                        repVO.setDesc((String) hashRows.get("CALDESC"));
                        repVO.setDataprec((String) hashRows.get("DATAPREC"));
                        repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                        repVO.setDepartment((String) hashRows.get("DEPARTMENT"));
                        repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
                        repVO.setDatatype((String) hashRows.get("DATATYPE"));
                        repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
                        repVO.setReffact((String) hashRows.get("REFFACT"));
                        repVO.setRepvaluecol(((String) hashRows.get("REPVALUECOL")).toUpperCase());
                        repVO.setIssum((String) hashRows.get("ISSUM"));
                        repVO.setIsvalid((String) hashRows.get("ISVALID"));
                        repVO.setRepDemLst(repDemLst);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return repVO;
    }

    public String getDimCodeByID(String dimtablename, String dimidcol, String dimcodecol, String dimidval) {
        String sret = "";
        try {
            DAO dao = new DAO();
            String sql = "select " + dimcodecol + " from " + dimtablename + " where " + dimidcol + "=" + dimidval + "";
            sret = dao.getDataValue(ConstUtil.DBNAMEODS, sql);

        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);

        }
        return sret;
    }
    
      public StringBuffer clear_char(StringBuffer bstr){
                String temp=bstr.toString();
                temp=temp.trim();
                bstr=new StringBuffer();
                bstr.append(temp);
            while(true){
            if(bstr.charAt(0)==','){
                bstr.deleteCharAt(0);
            }
            else{
                break;
            }
            }
            while(true){
            if(bstr.charAt(bstr.toString().length()-1)==','){
                 bstr.deleteCharAt(bstr.length()-1);
            }
            else{
                break;
            }
            }
            return bstr;
            }
      
       public String getDataGrid(String repid,String corpid,String demJsonArr) {
       String res = "";
           RepDataVO repVo = getRepDemension(repid);
           if (repVo != null) {
           String tablename = repVo.getReffact(); //TF_REP_HRCNT
           res=tablename;
           FactTableVO.setSql(res);
           }
       
       return res;
   }
       
       
        public boolean isSelect(String facdimcol,String demJsonArr){
          DAO dao=new DAO();
          boolean b=true;
          JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtable");
	                String facdimcodecol = jsonDem.getString("facdimcodecol");
	                String facdimvalue = jsonDem.getString("facdimvalue");
                        String sql="SELECT t.DIMTABLE FROM tb_demension t WHERE t.FACDIMCOL='"+facdimcol+"'";
                        
              try {
                  String value=dao.getDataValue(ConstUtil.DBNAME, sql);
                  if(value.equals(facdimtable)){
                      if ( facdimvalue.equals(this.DemIsNotSelect)){
	                	b=false;
	                }
                  }
              } catch (ServletException ex) {
                  Logger.getLogger(FillDataDao_Wan.class.getName()).log(Level.SEVERE, null, ex);
              }
	                
	            }
          return b;
      }
        
        /**
         * 导出数据采集模板专用
         * @param facdimcol
         * @param demJsonArr
         * @return
         */
        public boolean isSelect2(String facdimcol,String demJsonArr){
            DAO dao=new DAO();
            boolean b=true;
            JSONObject qryjson = JSONObject.fromString(demJsonArr);
  	            JSONArray demArr = qryjson.getJSONArray("demlist");
  	            for (int i = 0; i < demArr.length(); i++)
  	            {
  	            	JSONObject jsonDem = demArr.getJSONObject(i);
  	                String facdimtable = jsonDem.getString("facdimtablewan");
  	                String facdimcodecol = jsonDem.getString("facdimcodecolwan");
  	                String facdimvalue = jsonDem.getString("facdimvaluewan");
                          String sql="SELECT t.DIMTABLE FROM tb_demension t WHERE t.FACDIMCOL='"+facdimcol+"'";
                          
                try {
                    String value=dao.getDataValue(ConstUtil.DBNAME, sql);
                    if(value.equals(facdimtable)){
                        if ( facdimvalue.equals("")){
  	                	b=false;
  	                }
                    }
                } catch (ServletException ex) {
                    Logger.getLogger(FillDataDao_Wan.class.getName()).log(Level.SEVERE, null, ex);
                }
  	                
  	            }
            return b;
        }
        
        public boolean doNotWrite(String value,List<String> list){
            boolean b=true;
            for(int i=0;i<list.size();i++){
                if(value.equals(list.get(i))){
                    b=false;
                    break;
                }
            }
            
            return b;
        }
}
